<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class DataRealisasiPendapatan {

    private $db;
    private $_period_name;
    private $_satker;
    private $_code_id;
    private $_kppn;
    private $_akun;
    private $_program;
    private $_output;
    private $_dana;
    private $_bank;
    private $_kewenangan;
    private $_lokasi;
    private $_budget_type;
    private $_currency_code;
    private $_budget_amt;
    private $_encumbrance_amt;
    private $_actual_amt;
    private $_balancing_amt;
    private $_nm_satker;
    private $_obligation;
    private $_block_amount;
    private $_temp_block;
    private $_cash_limit;
	private $_pengembalian;
    private $_invoice;
	private $_tgl;
	private $_nodok;
	private $_jan;
	private $_feb;
	private $_mar;
	private $_apr;
	private $_mei;
	private $_jun;
	private $_jul;
	private $_agt;
	private $_sep;
	private $_okt;
	private $_nov;
	private $_des;
	private $_mpn2_pajak;
	private $_mpn2_nonpajak;
	private $_mpn1_pajak;
	private $_mpn1_nonpajak;
	private $_dtc_pajak;
	private $_dtc_nonpajak;
	private $_bi_nonpajak;
	private $_bi_pajak;
    private $_table1 = 'GL_BALANCES_V';
    private $_table2 = 'T_BA';
    private $_table3 = 'T_KEGIATAN';
    private $_table4 = 'T_ESELON1';
    private $_table6 = 'T_OUTPUT';
    private $_table5 = 'T_AKUN';
    private $_table7 = 'BLOCK_REVISI';
    private $_table8= 'T_SATKER_ALL';
	private $_table9= 'PENGEMBALIAN_PEND';
	private $_table10= 'DETAIL_PENGEMBALIAN_PEND';
    public $registry;

    /*
     * konstruktor
     */

    public function __construct($registry = Registry) {
        $this->db = $registry->db;
        $this->registry = $registry;

        if (('' . Session::get('ta')) == date("Y")) {
            $this->_table1 = 'GL_BALANCES_V';
            $this->_table8 = 'T_SATKER';
        } else {
            $this->_table1 = 'GL_BALANCES_V_TL';
            $this->_table8 = 't_satker_tl';
        }
        $this->_table7="(SELECT * FROM BLOCK_REVISI WHERE PERIOD_YEAR=".Session::get('ta').")";
    }

    /*
     * mendapatkan data dari tabel Data Tetap
     * @param limit batas default null
     * return array objek Data Tetap */





    /* ----------------------------------------------------------------------------------------------------------- contekan */

    
	/* DARI GL_BALANCES_V
    public function get_ba_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = " SELECT A.AKUN, A.DESCRIPTION, SUM(PAGU) PAGU	,SUM(D.TOTAL) POTONGAN, SUM(REALISASI) - NVL(SUM(D.TOTAL),0) SETORAN, SUM(REALISASI) REALISASI, A.KDBA
		FROM (
		SELECT A.SATKER, A.AKUN, C.DESCRIPTION, SUM(BUDGET_AMT) PAGU
		,  SUM(ACTUAL_AMT) * -1 REALISASI, B.BA KDBA
				FROM "
                . $this->_table1 . " A, "
                . $this->_table5 . " C , 
				( SELECT DISTINCT KDSATKER, BA, KANWIL_DJPB, BAES1 FROM " . $this->_table8 . " ) B  				
				WHERE 1=1 
				AND A.AKUN =C.FLEX_VALUE			
				AND A.SUMMARY_FLAG = 'N' 
				AND SUBSTR(A.AKUN,1,1) = '4'
				AND BUDGET_TYPE IN ('2','7')
				AND A.SATKER = B.KDSATKER								
				AND NVL(A.BUDGET_AMT,0) + NVL(A.ENCUMBRANCE_AMT,0) + NVL(A.ACTUAL_AMT,0) <> 0
				
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY A.AKUN, C.DESCRIPTION, B.BA, A.SATKER )A  ";
		$sql .= " LEFT JOIN
					( SELECT DISTINCT AKUN, SATKER, SUM(NILAI_ORI)TOTAL FROM SPPM_BPN WHERE SUBSTR(JENDOK_EXIS,1,1) NOT IN ('3','4','5','6') AND BUDGET = '2' GROUP BY AKUN, SATKER ) D
					ON A.SATKER = D.SATKER
					AND A.AKUN = D.AKUN ";
		 $sql .= " GROUP BY A.AKUN,  A.DESCRIPTION,  A.KDBA ";

        $sql .= " ORDER BY A.AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
            $data[] = $d_data;
        }
        return $data;
    }
	*/
	public function get_ba_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = " SELECT 
					A.AKUN,
					C.DESCRIPTION,
					SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual') AND BUD_TYPE = '2' THEN RPHREAL *-1 ELSE 0 END) POTONGAN,
					SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual','Receivables') AND BUD_TYPE = '7' THEN RPHREAL *-1 ELSE 0 END) PENGEMBALIAN,
					SUM(CASE WHEN JE_SOURCE IN ('Receivables','Conversion') THEN RPHREAL *-1 ELSE 0 END) SETORAN,
					SUM(RPHREAL *-1) REALISASI
						FROM
					GL_JE_LINES A,
					T_AKUN C,
					( SELECT DISTINCT KDSATKER, BA, KANWIL_DJPB, BAES1 FROM T_SATKER_ALL  ) B
					WHERE A.AKUN = C.FLEX_VALUE (+)
					AND SUBSTR(A.AKUN,1,1) = '4'
					AND A.KDSATKER = B.KDSATKER(+)					
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY A.AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
			$d_data->set_pengembalian($val['PENGEMBALIAN']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_blu_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = " SELECT
				 A.AKUN
				, C.DESCRIPTION
				, SUM(CASE WHEN JE_SOURCE IN ('DIPA') AND FLAG = 'B' THEN AMOUNT  ELSE 0 END) PAGU
				, SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual') AND BUD_TYPE    = '2' AND FLAG = 'A' THEN AMOUNT  ELSE 0 END) POTONGAN
				, SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual','Receivables') AND BUD_TYPE    = '7' AND FLAG = 'A' THEN AMOUNT  ELSE 0 END) PENGEMBALIAN
				, SUM(CASE WHEN JE_SOURCE IN ('Receivables','Conversion') AND FLAG = 'A' THEN AMOUNT  ELSE 0 END) SETORAN
				, SUM(CASE WHEN FLAG = 'A' THEN AMOUNT  ELSE 0 END) REALISASI
				FROM DM_PENDAPATAN_PER_SATKER A,
				T_AKUN C
				WHERE A.AKUN           = C.FLEX_VALUE (+)	
				AND SUBSTR(AKUN,1,3) = '424' 				
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY A.AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
			$d_data->set_pengembalian($val['PENGEMBALIAN']);
            $data[] = $d_data;
        }
        return $data;
    }
	//TEST MENKEU PER AKUN LAYER DARI PER KL
	public function get_ba_pendapatan_per_akun_menkeu($filter) {
        Session::get('id_user');
        $sql = " SELECT 
					A.AKUN,
					C.DESCRIPTION,
					SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual') AND BUD_TYPE = '2' THEN RPHREAL *-1 ELSE 0 END) POTONGAN,
					SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual') AND BUD_TYPE = '7' THEN RPHREAL *-1 ELSE 0 END) PENGEMBALIAN,
					SUM(CASE WHEN JE_SOURCE IN ('Receivables','Conversion') THEN RPHREAL *-1 ELSE 0 END) SETORAN,
					SUM(RPHREAL *-1) REALISASI
						FROM
					GL_JE_LINES A,
					T_AKUN C,
					( SELECT DISTINCT KDSATKER, BA FROM T_SATKER_ALL  ) B
					WHERE A.AKUN = C.FLEX_VALUE (+)
					AND SUBSTR(A.AKUN,1,1) = '4'
					AND A.KDSATKER = B.KDSATKER(+)					
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY A.AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
			$d_data->set_pengembalian($val['PENGEMBALIAN']);
            $data[] = $d_data;
        }
        return $data;
    }
	/*
	public function get_ba_pendapatan_per_akun_kl($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GJL_PENDAPATAN_AKUN_KL WHERE 1=1
										
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY AKUN, DESCRIPTION ";
        $sql .= " ORDER BY AKUN ";

		//var_dump($sql);
        //echo($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
			$d_data->set_pengembalian($val['PENGEMBALIAN']);
            $data[] = $d_data;
        }
        return $data;
    }*/
	
	public function get_ba_pendapatan_per_akun_kl($filter) {
        Session::get('id_user');
        $sql = " SELECT
		 A.AKUN
		, C.DESCRIPTION
		, SUM(CASE WHEN JE_SOURCE IN ('DIPA') AND FLAG = 'B' THEN AMOUNT  ELSE 0 END) PAGU
		, SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual') AND BUD_TYPE    = '2' AND FLAG = 'A' THEN AMOUNT  ELSE 0 END) POTONGAN
		, SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual','Receivables') AND BUD_TYPE    = '7' AND FLAG = 'A' THEN AMOUNT  ELSE 0 END) PENGEMBALIAN
		, SUM(CASE WHEN JE_SOURCE IN ('Receivables','Conversion') AND FLAG = 'A' THEN AMOUNT  ELSE 0 END) SETORAN
		, SUM(CASE WHEN FLAG = 'A' THEN AMOUNT  ELSE 0 END) REALISASI
		FROM DM_PENDAPATAN_PER_SATKER A,
		T_AKUN C
		WHERE A.AKUN           = C.FLEX_VALUE (+)										
		";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY A.AKUN,C.DESCRIPTION ";
        $sql .= " ORDER BY AKUN ";

		//var_dump($sql);
        //echo($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
			$d_data->set_pengembalian($val['PENGEMBALIAN']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	/* DIUBAH KE FORMAT mpn 01062015
	public function get_ba_pendapatan_per_akun_satker($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GJL_PENDAPATAN_AKUN_SATKER WHERE 1=1
										
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY AKUN, DESCRIPTION ";
        $sql .= " ORDER BY AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
			$d_data->set_pengembalian($val['PENGEMBALIAN']);
            $data[] = $d_data;
        }
        return $data;
    }
	*/
	/* FORMAT MPN */
	public function get_ba_pendapatan_per_akun_satker($filter) {
        Session::get('id_user');
       
		$sql = " SELECT AKUN
		, DESCRIPTION
		, SUM(POTONGAN) POTONGAN
		, SUM(PENGEMBALIAN) PENGEMBALIAN
		, SUM(MPN2_PAJAK) MPN2_PAJAK
		, SUM(MPN2_NON_PAJAK) MPN2_NON_PAJAK
		, SUM(MPN1_PAJAK) MPN1_PAJAK
		, SUM(MPN1_NON_PAJAK) MPN1_NON_PAJAK
		, SUM(BI_PAJAK) BI_PAJAK
		, SUM(BI_NON_PAJAK) BI_NON_PAJAK
		, SUM(DTC_PAJAK) DTC_PAJAK
		, SUM(DTC_NON_PAJAK) DTC_NON_PAJAK
		, SUM(REALISASI) REALISASI
		FROM GJL_PENDAPATAN_AKUN_SATKER_NEW WHERE 1=1";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY AKUN, DESCRIPTION ";
        //$sql .= " ORDER BY A.AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt(round($val['REALISASI']),2);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana(round($val['POTONGAN']),2);
			$d_data->set_bank(round($val['SETORAN']),2);
			$d_data->set_pengembalian(round($val['PENGEMBALIAN']),2);
			$d_data->set_mpn2_pajak(round($val['MPN2_PAJAK']),2);
			$d_data->set_mpn2_nonpajak(round($val['MPN2_NON_PAJAK']),2);
			$d_data->set_mpn1_pajak(round($val['MPN1_PAJAK']),2);
			$d_data->set_mpn1_nonpajak(round($val['MPN1_NON_PAJAK']),2);
			$d_data->set_bi_pajak(round($val['BI_PAJAK']),2);
			$d_data->set_bi_nonpajak(round($val['BI_NON_PAJAK']),2);
			$d_data->set_dtc_pajak(round($val['DTC_PAJAK']),2);
			$d_data->set_dtc_nonpajak(round($val['DTC_NON_PAJAK']),2);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_ba_pendapatan_menkeu($filter) {
        Session::get('id_user');
        //diubah ke satu table biar performa oke
		/*$sql = " SELECT 
					A.AKUN,
					C.DESCRIPTION,
					SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual') AND BUD_TYPE = '2' THEN RPHREAL *-1 ELSE 0 END) POTONGAN,
					SUM(CASE WHEN JE_SOURCE IN ('Payables','Manual') AND BUD_TYPE = '7' THEN RPHREAL *-1 ELSE 0 END) PENGEMBALIAN,
					SUM(CASE WHEN JE_SOURCE IN ('Receivables','Conversion') THEN RPHREAL *-1 ELSE 0 END) SETORAN,
					SUM(RPHREAL *-1) REALISASI
						FROM
					GL_JE_LINES A,
					T_AKUN C,
					(SELECT DISTINCT KDSATKER, CASE WHEN KDSATKER LIKE 'Z%' THEN 'ZZZ' ELSE BA END BA FROM T_SATKER) B
					WHERE A.AKUN = C.FLEX_VALUE (+)
					AND SUBSTR(A.AKUN,1,1) = '4'
					AND TGLPOST <= TRUNC(SYSDATE-1)
					AND A.KDSATKER = B.KDSATKER(+)					
				";*/
		$sql = " SELECT * FROM PENDAPATAN_PER_AKUN_MENKEU_NEW ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        //$sql .= " ORDER BY A.AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt(round($val['REALISASI']),2);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana(round($val['POTONGAN']),2);
			$d_data->set_bank(round($val['SETORAN']),2);
			$d_data->set_pengembalian(round($val['PENGEMBALIAN']),2);
			$d_data->set_mpn2_pajak(round($val['MPN2_PAJAK']),2);
			$d_data->set_mpn2_nonpajak(round($val['MPN2_NON_PAJAK']),2);
			$d_data->set_mpn1_pajak(round($val['MPN1_PAJAK']),2);
			$d_data->set_mpn1_nonpajak(round($val['MPN1_NON_PAJAK']),2);
			$d_data->set_bi_pajak(round($val['BI_PAJAK']),2);
			$d_data->set_bi_nonpajak(round($val['BI_NON_PAJAK']),2);
			$d_data->set_dtc_pajak(round($val['DTC_PAJAK']),2);
			$d_data->set_dtc_nonpajak(round($val['DTC_NON_PAJAK']),2);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_pengembalian_pendapatan($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM " 				
				. $this->_table9. " WHERE 1=1 ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_program($val['BAES1']);
            $d_data->set_jan($val['JAN']);
            $d_data->set_feb($val['FEB']);
            $d_data->set_mar($val['MAR']);
            $d_data->set_apr($val['APR']);
            $d_data->set_mei($val['MEI']);
			$d_data->set_jun($val['JUN']);
			$d_data->set_jul($val['JUL']);
			$d_data->set_agt($val['AGT']);
			$d_data->set_sep($val['SEP']);
			$d_data->set_okt($val['OKT']);
			$d_data->set_nov($val['NOV']);
			$d_data->set_des($val['DES']);
			$d_data->set_balancing_amt($val['TOTAL']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_pendapatan_pajak($filter) {
        Session::get('id_user');
        $sql = " select 
				case when baes1 <> '01504' then '01504Z' else kdsatker end kdsatker, 
				case when (baes1 <> '01504' or kdsatker = '01504Z') then 'PENERIMAAN DARI LUAR DJP DAN SUSPEND UNTUK DIKOREKSI' else nmsatker end nmsatker,
				sum(case when periode = '01' then amount else 0 end) jan,
				sum(case when periode = '02' then amount else 0 end) feb,
				sum(case when periode = '03' then amount else 0 end) mar,
				sum(case when periode = '04' then amount else 0 end) apr,
				sum(case when periode = '05' then amount else 0 end) mei,
				sum(case when periode = '06' then amount else 0 end) jun,
				sum(case when periode = '07' then amount else 0 end) jul,
				sum(case when periode = '08' then amount else 0 end) agt,
				sum(case when periode = '09' then amount else 0 end) sep,
				sum(case when periode = '10' then amount else 0 end) okt,
				sum(case when periode = '11' then amount else 0 end) nov,
				sum(case when periode = '12' then amount else 0 end) des,
				sum(amount)total
				from dm_pendapatan_per_satker 
				where 1=1
				and substr(akun,1,2) = '41'
				and substr(akun,1,4) <> '4115'
				and substr(akun,1,3) <>  '412' 
				and flag ='A'
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " group by case when baes1 <> '01504' then '01504Z' else kdsatker end, 
				case when (baes1 <> '01504' or kdsatker = '01504Z') then 'PENERIMAAN DARI LUAR DJP DAN SUSPEND UNTUK DIKOREKSI' else nmsatker end
				having sum(amount) > 0"
				;
        $sql .= " ORDER BY  KDSATKER DESC";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_program($val['BAES1']);
            $d_data->set_jan($val['JAN']);
            $d_data->set_feb($val['FEB']);
            $d_data->set_mar($val['MAR']);
            $d_data->set_apr($val['APR']);
            $d_data->set_mei($val['MEI']);
			$d_data->set_jun($val['JUN']);
			$d_data->set_jul($val['JUL']);
			$d_data->set_agt($val['AGT']);
			$d_data->set_sep($val['SEP']);
			$d_data->set_okt($val['OKT']);
			$d_data->set_nov($val['NOV']);
			$d_data->set_des($val['DES']);
			$d_data->set_balancing_amt($val['TOTAL']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_pendapatan_cukai($filter) {
        Session::get('id_user');
        $sql = " select 
					case when baes1 <> '01505' then '01505Z' else kdsatker end kdsatker, 
					case when (baes1 <> '01505' or kdsatker = '01505Z') then 'PENERIMAAN DARI LUAR DJBC DAN SUSPEND UNTUK DIKOREKSI' else nmsatker end nmsatker,
					sum(case when periode = '01' then amount else 0 end) jan,
					sum(case when periode = '02' then amount else 0 end) feb,
					sum(case when periode = '03' then amount else 0 end) mar,
					sum(case when periode = '04' then amount else 0 end) apr,
					sum(case when periode = '05' then amount else 0 end) mei,
					sum(case when periode = '06' then amount else 0 end) jun,
					sum(case when periode = '07' then amount else 0 end) jul,
					sum(case when periode = '08' then amount else 0 end) agt,
					sum(case when periode = '09' then amount else 0 end) sep,
					sum(case when periode = '10' then amount else 0 end) okt,
					sum(case when periode = '11' then amount else 0 end) nov,
					sum(case when periode = '12' then amount else 0 end) des,
					sum(amount)total
					from dm_pendapatan_per_satker 
					where 1=1
					and (substr(akun,1,4) = '4115' or  substr(akun,1,3) = '412')
					and flag ='A'
					";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY case when baes1 <> '01505' then '01505Z' else kdsatker end, 
					case when (baes1 <> '01505' or kdsatker = '01505Z') then 'PENERIMAAN DARI LUAR DJBC DAN SUSPEND UNTUK DIKOREKSI' else nmsatker end
					having sum(amount) > 0"
				;
        $sql .= " ORDER BY  KDSATKER DESC";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_program($val['BAES1']);
            $d_data->set_jan($val['JAN']);
            $d_data->set_feb($val['FEB']);
            $d_data->set_mar($val['MAR']);
            $d_data->set_apr($val['APR']);
            $d_data->set_mei($val['MEI']);
			$d_data->set_jun($val['JUN']);
			$d_data->set_jul($val['JUL']);
			$d_data->set_agt($val['AGT']);
			$d_data->set_sep($val['SEP']);
			$d_data->set_okt($val['OKT']);
			$d_data->set_nov($val['NOV']);
			$d_data->set_des($val['DES']);
			$d_data->set_balancing_amt($val['TOTAL']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_pendapatan_pnbp($filter) {
        Session::get('id_user');
        $sql = " select 
					case when kdsatker like '%Z%' then 'ZZZZZZ' else kdsatker end kdsatker, 
					case when kdsatker like '%Z%' then 'SUSPEND PENERIMAAN' else nmsatker end nmsatker,
					sum(case when periode = '01' then amount else 0 end) jan,
					sum(case when periode = '02' then amount else 0 end) feb,
					sum(case when periode = '03' then amount else 0 end) mar,
					sum(case when periode = '04' then amount else 0 end) apr,
					sum(case when periode = '05' then amount else 0 end) mei,
					sum(case when periode = '06' then amount else 0 end) jun,
					sum(case when periode = '07' then amount else 0 end) jul,
					sum(case when periode = '08' then amount else 0 end) agt,
					sum(case when periode = '09' then amount else 0 end) sep,
					sum(case when periode = '10' then amount else 0 end) okt,
					sum(case when periode = '11' then amount else 0 end) nov,
					sum(case when periode = '12' then amount else 0 end) des,
					sum(amount)total
					from dm_pendapatan_per_satker 
					where 1=1
					and substr(akun,1,2) = '42'
					and flag ='A'";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY case when kdsatker like '%Z%' then 'ZZZZZZ' else kdsatker end , 
					case when kdsatker like '%Z%' then 'SUSPEND PENERIMAAN' else nmsatker end "
				;
	
		
        $sql .= " ORDER BY  KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_program($val['BAES1']);
            $d_data->set_jan($val['JAN']);
            $d_data->set_feb($val['FEB']);
            $d_data->set_mar($val['MAR']);
            $d_data->set_apr($val['APR']);
            $d_data->set_mei($val['MEI']);
			$d_data->set_jun($val['JUN']);
			$d_data->set_jul($val['JUL']);
			$d_data->set_agt($val['AGT']);
			$d_data->set_sep($val['SEP']);
			$d_data->set_okt($val['OKT']);
			$d_data->set_nov($val['NOV']);
			$d_data->set_des($val['DES']);
			$d_data->set_balancing_amt($val['TOTAL']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_pendapatan_pnbp_global($filter) {
        Session::get('id_user');
        $sql = " select 
					case when ba like '%Z%' then 'ZZZZZZ' else ba end kdsatker, 
					case when nmba like '%Z%' then 'SUSPEND PENERIMAAN' else nmba end nmsatker,
					sum(case when periode = '01' then amount else 0 end) jan,
					sum(case when periode = '02' then amount else 0 end) feb,
					sum(case when periode = '03' then amount else 0 end) mar,
					sum(case when periode = '04' then amount else 0 end) apr,
					sum(case when periode = '05' then amount else 0 end) mei,
					sum(case when periode = '06' then amount else 0 end) jun,
					sum(case when periode = '07' then amount else 0 end) jul,
					sum(case when periode = '08' then amount else 0 end) agt,
					sum(case when periode = '09' then amount else 0 end) sep,
					sum(case when periode = '10' then amount else 0 end) okt,
					sum(case when periode = '11' then amount else 0 end) nov,
					sum(case when periode = '12' then amount else 0 end) des,
					sum(amount)total
					from dm_pendapatan_per_satker a,
					t_ba b
					where 1=1
					and a.ba = b.kdba
					and substr(akun,1,2) = '42'
					and flag ='A'";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY case when ba like '%Z%' then 'ZZZZZZ' else ba end , 
					case when nmba like '%Z%' then 'SUSPEND PENERIMAAN' else nmba end  "
				;
	
		
        $sql .= " ORDER BY  KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_program($val['BAES1']);
            $d_data->set_jan($val['JAN']);
            $d_data->set_feb($val['FEB']);
            $d_data->set_mar($val['MAR']);
            $d_data->set_apr($val['APR']);
            $d_data->set_mei($val['MEI']);
			$d_data->set_jun($val['JUN']);
			$d_data->set_jul($val['JUL']);
			$d_data->set_agt($val['AGT']);
			$d_data->set_sep($val['SEP']);
			$d_data->set_okt($val['OKT']);
			$d_data->set_nov($val['NOV']);
			$d_data->set_des($val['DES']);
			$d_data->set_balancing_amt($val['TOTAL']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_detail_pengembalian_pendapatan($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM " 				
				. $this->_table10. " WHERE 1=1 ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);			
			$d_data->set_akun($val['AKUN']);
			$d_data->set_pengembalian($val['DESCRIPTION']);
			$d_data->set_balancing_amt($val['RPHREAL']);
			$d_data->set_tgl($val['TGLPOST']);
			$d_data->set_nodok($val['NODOK']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_detail_pengembalian_pendapatan_baes1($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM " 				
				. $this->_table10. " A ,
				(SELECT DISTINCT KDSATKER SATKER, BA,BAES1 FROM T_SATKER_ALL) B

				WHERE A.KDSATKER = SATKER ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY A.KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);			
			$d_data->set_akun($val['AKUN']);
			$d_data->set_pengembalian($val['DESCRIPTION']);
			$d_data->set_balancing_amt($val['RPHREAL']);
			$d_data->set_tgl($val['TGLPOST']);
			$d_data->set_nodok($val['NODOK']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_detail_akun_potongan($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GL_JE_LINES_POTONGAN WHERE 1=1 ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_kppn($val['KPPN']);
			$d_data->set_akun($val['DESCRIPTION']);
			$d_data->set_pengembalian($val['BAES1']);
			$d_data->set_balancing_amt($val['RPHREAL']);
			$d_data->set_tgl($val['TGLPOST']);
			$d_data->set_nodok($val['NODOK']);
			
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_detail_akun_potongan_kanwil($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GL_JE_LINES_POTONGAN A, (SELECT KDSATKER SATKER, KANWIL_DJPB FROM T_SATKER_ALL) B
				WHERE 1=1 AND A.KDSATKER = B.SATKER ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_kppn($val['KPPN']);
			$d_data->set_akun($val['DESCRIPTION']);
			$d_data->set_pengembalian($val['BAES1']);
			$d_data->set_balancing_amt($val['RPHREAL']);
			$d_data->set_tgl($val['TGLPOST']);
			$d_data->set_nodok($val['NODOK']);
			
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_detail_akun_pengembalian($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GL_JE_LINES_PENGEMBALIAN WHERE 1=1 ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_kppn($val['KPPN']);
			$d_data->set_akun($val['DESCRIPTION']);
			$d_data->set_pengembalian($val['BAES1']);
			$d_data->set_balancing_amt($val['RPHREAL']);
			$d_data->set_tgl($val['TGLPOST']);
			$d_data->set_nodok($val['NODOK']);
			
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_detail_akun_pengembalian_kanwil($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GL_JE_LINES_PENGEMBALIAN A, (SELECT KDSATKER SATKER, KANWIL_DJPB FROM T_SATKER_ALL) B
				WHERE 1=1 AND A.KDSATKER = B.SATKER ";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY A.AKUN, C.DESCRIPTION ";
        $sql .= " ORDER BY KDSATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDSATKER']);
			$d_data->set_nm_satker($val['NMSATKER']);
			$d_data->set_kppn($val['KPPN']);
			$d_data->set_akun($val['DESCRIPTION']);
			$d_data->set_pengembalian($val['BAES1']);
			$d_data->set_balancing_amt($val['RPHREAL']);
			$d_data->set_tgl($val['TGLPOST']);
			$d_data->set_nodok($val['NODOK']);
			
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_kanwil_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = " SELECT AKUN, DESCRIPTION, SUM(POTONGAN) POTONGAN, SUM(PENGEMBALIAN) PENGEMBALIAN, SUM(SETORAN) SETORAN
					, SUM(REALISASI) REALISASI
					FROM GJL_PENDAPATAN_AKUN_SATKER A, (SELECT KDSATKER SATKER, KANWIL_DJPB FROM T_SATKER_ALL) B
					WHERE 1=1
					AND A.KDSATKER = B.SATKER";
		foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
		$sql .= " GROUP BY A.AKUN,  A.DESCRIPTION ";
		

        $sql .= " ORDER BY A.AKUN ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['AKUN']);
            $d_data->set_nmkegiatan($val['DESCRIPTION']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            $d_data->set_nm_satker($val['NMBA']);
			$d_data->set_dana($val['POTONGAN']);
			$d_data->set_bank($val['SETORAN']);
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_ba_per_satker_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = "SELECT C.NMSATKER, SUM(BUDGET_AMT) PAGU, SUM(ACTUAL_AMT)* -1 REALISASI, A.SATKER, SUBSTR(PROGRAM,1,5) KDBA
				FROM "
                . $this->_table1 . " A, 
                ( SELECT DISTINCT KDSATKER, nmsatker,BA, BAES1 FROM " . $this->_table8.")  C 
				WHERE 1=1 
				AND A.SATKER =C.KDSATKER			
				AND A.SUMMARY_FLAG = 'N' 
				AND SUBSTR(AKUN,1,1) = '4'
				AND BUDGET_TYPE IN ('2','7')
				AND NVL(A.BUDGET_AMT,0) + NVL(A.ENCUMBRANCE_AMT,0) + NVL(A.ACTUAL_AMT,0) <> 0
				
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY A.SATKER, C.NMSATKER ";
        $sql .= " ORDER BY A.SATKER ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['SATKER']);
            $d_data->set_nmkegiatan($val['NMSATKER']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	
	
    
	public function get_ba_per_es1_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = "SELECT C.NMES1,
					 SUM(CASE WHEN JE_SOURCE IN ('DIPA') AND FLAG = 'B' THEN AMOUNT  ELSE 0 END) PAGU ,
					 SUM(CASE WHEN FLAG = 'A' THEN AMOUNT  ELSE 0 END)  REALISASI,
					  BAES1 KDES1
					FROM DM_PENDAPATAN_PER_SATKER A,
					  T_ESELON1 C 
					WHERE 1                =1
					AND A.BAES1 = C.KDES1
														
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY BAES1, C.NMES1 ";
        $sql .= " ORDER BY BAES1 ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['KDES1']);
            $d_data->set_nmkegiatan($val['NMES1']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_ba_per_kl_pendapatan_filter($filter) {
        Session::get('id_user');
         $sql = "SELECT BA, C.NMBA,
					 SUM(CASE WHEN JE_SOURCE IN ('DIPA') AND FLAG = 'B' THEN AMOUNT  ELSE 0 END) PAGU ,
					 SUM(CASE WHEN FLAG = 'A' THEN AMOUNT  ELSE 0 END)  REALISASI
					
					FROM DM_PENDAPATAN_PER_SATKER A,
					  T_BA C 
					WHERE 1                =1
					AND A.BA = C.KDBA
														
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        $sql .= " GROUP BY BA, C.NMBA ";
        $sql .= " ORDER BY BA ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['BA']);
            $d_data->set_kdkegiatan($val['BA']);
            $d_data->set_nmkegiatan($val['NMBA']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_per_kl_pendapatan_menkeu($filter) {
        Session::get('id_user');
        $sql = "SELECT * FROM PENDAPATAN_PER_KL_MENKEU
										
				";
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }
        //$sql .= " GROUP BY B.BA, C.NMBA ";
        //$sql .= " ORDER BY B.BA ";


        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['KDES1']);
            $d_data->set_nmkegiatan($val['NMES1']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_kl_per_es1satker_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = "SELECT 
				BA,
				BAES1,
				BAES1 KODE,
				C.NMES1 NAMA,
				SUM(CASE WHEN FLAG = 'B' THEN AMOUNT ELSE 0 END) PAGU,
				SUM(CASE WHEN FLAG = 'A' THEN AMOUNT ELSE 0 END) REALISASI
				FROM DM_PENDAPATAN_PER_SATKER A,
				  T_ESELON1 C
				WHERE 1              =1
				AND C.KDES1          = A.BAES1								
				";
        $no = 0;
        foreach ($filter as $filter1) {
            $sql .= " AND " . $filter1;
        }
        $sql .= " GROUP BY  BAES1,BA,C.NMES1 ";
		$sql .= " UNION ALL ";
		$sql .= "SELECT 
				BA,
				BAES1,
				BAES1
				  || '-'
				  || KDSATKER KODE,
				  UPPER(NMSATKER) NAMA,
				SUM(CASE WHEN FLAG = 'B' THEN AMOUNT ELSE 0 END) PAGU,
				SUM(CASE WHEN FLAG = 'A' THEN AMOUNT ELSE 0 END) REALISASI
				FROM DM_PENDAPATAN_PER_SATKER A 
				WHERE 1              =1
				AND SUBSTR(AKUN,1,1) = '4'											
				";
        $no = 0;
        foreach ($filter as $filter2) {
            $sql .= " AND " . $filter2;
        }
        $sql .= " GROUP BY BA, BAES1,KDSATKER,NMSATKER ";
        $sql .= " ORDER BY KODE ";
        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['KODE']);
            $d_data->set_nmkegiatan($val['NAMA']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	/*ROLLBACK BY JHON 20150428
	public function get_kl_per_es1satker_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GJL_PENDAPATAN_SATKER_KL WHERE 1=1 ";
		$no = 0;
        foreach ($filter as $filter1) {
            $sql .= " AND " . $filter1;
        }
        //$sql .= " GROUP BY BAES1, A.KDSATKER, B.NMSATKER ";
        $sql .= " ORDER BY KODE ";
        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['KODE']);
            $d_data->set_nmkegiatan($val['NAMA']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	ROLLBACK BY JHON 20150428*/
	
	public function get_pendapatan_persatker_blu_filter($filter) {
        Session::get('id_user');
         $sql = "SELECT 
				BA,
				BAES1,
				BAES1 KODE,
				C.NMES1 NAMA,
				SUM(CASE WHEN FLAG = 'B' THEN AMOUNT ELSE 0 END) PAGU,
				SUM(CASE WHEN FLAG = 'A' THEN AMOUNT ELSE 0 END) REALISASI
				FROM DM_PENDAPATAN_PER_SATKER A,
				  T_ESELON1 C,
				  RUMPUN_BLU D
				WHERE 1              =1
				AND A.KDSATKER = D.KDSATKER
				AND C.KDES1          = A.BAES1		
				AND SUBSTR(AKUN,1,3) = '424'
				";
        $no = 0;
        foreach ($filter as $filter1) {
            $sql .= " AND " . $filter1;
        }
        $sql .= " GROUP BY  BAES1,BA,C.NMES1 ";
		$sql .= " UNION ALL ";
		$sql .= "SELECT 
				BA,
				BAES1,
				BAES1
				  || '-'
				  || A.KDSATKER KODE,
				  UPPER(A.NMSATKER) NAMA,
				SUM(CASE WHEN FLAG = 'B' THEN AMOUNT ELSE 0 END) PAGU,
				SUM(CASE WHEN FLAG = 'A' THEN AMOUNT ELSE 0 END) REALISASI
				FROM DM_PENDAPATAN_PER_SATKER A ,
				RUMPUN_BLU B
				WHERE 1              =1
				AND A.KDSATKER = B.KDSATKER
				AND SUBSTR(AKUN,1,3) = '424'										
				";
        $no = 0;
        foreach ($filter as $filter2) {
            $sql .= " AND " . $filter2;
        }
        $sql .= " GROUP BY BA, BAES1,A.KDSATKER,A.NMSATKER ";
        $sql .= " ORDER BY KODE ";
        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['KODE']);
            $d_data->set_nmkegiatan($val['NAMA']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_kanwil_per_es1satker_pendapatan_filter($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM GJL_PENDAPATAN_SATKER_KANWIL
				WHERE 1=1
				";
        $no = 0;
        foreach ($filter as $filter1) {
            $sql .= " AND " . $filter1;
        }
        //$sql .= " GROUP BY BAES1, A.SATKER, B.NMSATKER ";
        $sql .= " ORDER BY KODE ";
        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
            $d_data->set_satker($val['KDBA']);
            $d_data->set_kdkegiatan($val['KODE']);
            $d_data->set_nmkegiatan($val['NAMA']);
            $d_data->set_budget_amt($val['PAGU']);
            $d_data->set_actual_amt($val['REALISASI']);
            
            $data[] = $d_data;
        }
        return $data;
    }
	

    /*
     * setter
     */
	public function set_jan($jan) {
        $this->_jan = $jan;
    }
	
	public function set_feb($feb) {
        $this->_feb = $feb;
    }
	
	public function set_mar($mar) {
        $this->_mar = $mar;
    }
	
	public function set_apr($apr) {
        $this->_apr = $apr;
    }
	
	public function set_mei($mei) {
        $this->_mei = $mei;
    }
	
	public function set_jun($jun) {
        $this->_jun = $jun;
    }
	
	public function set_jul($jul) {
        $this->_jul = $jul;
    }
	
	public function set_agt($agt) {
        $this->_agt = $agt;
    }
	
	public function set_sep($sep) {
        $this->_sep = $sep;
    }
	
	public function set_okt($okt) {
        $this->_okt = $okt;
    }
	
	public function set_nov($nov) {
        $this->_nov = $nov;
    }
	
	public function set_des($des) {
        $this->_des = $des;
    }
	
    public function set_obligation($obligation) {
        $this->_obligation = $obligation;
    }

    public function set_block_amount($block_amont) {
        $this->_block_amount = $block_amont;
    }

    public function set_temp_block($temp_block) {
        $this->_temp_block = $temp_block;
    }

    public function set_cash_limit($cash_limit) {
        $this->_cash_limit = $cash_limit;
    }

    public function set_invoice($invoice) {
        $this->_invoice = $invoice;
    }

    public function set_satker($satker) {
        $this->_satker = $satker;
    }

    public function set_kppn($kppn) {
        $this->_kppn = $kppn;
    }

    public function set_nm_satker($nm_satker) {
        $this->_nm_satker = $nm_satker;
    }

    public function set_code_id($code_id) {
        $this->_code_id = $code_id;
    }

    public function set_akun($akun) {
        $this->_akun = $akun;
    }

    public function set_program($program) {
        $this->_program = $program;
    }

    public function set_output($output) {
        $this->_output = $output;
    }

    public function set_dana($dana) {
        $this->_dana = $dana;
    }

    public function set_bank($bank) {
        $this->_bank = $bank;
    }

    public function set_kewenangan($kewenangan) {
        $this->_kewenangan = $kewenangan;
    }

    public function set_lokasi($lokasi) {
        $this->_lokasi = $lokasi;
    }

    public function set_budget_type($budget_type) {
        $this->_budget_type = $budget_type;
    }

    public function set_currency_code($currency_code) {
        $this->_currency_code = $currency_code;
    }

    public function set_budget_amt($budget_amt) {
        $this->_budget_amt = $budget_amt;
    }

    public function set_encumbrance_amt($encumbrance_amt) {
        $this->_encumbrance_amt = $encumbrance_amt;
    }

    public function set_actual_amt($actual_amt) {
        $this->_actual_amt = $actual_amt;
    }

    public function set_balancing_amt($balancing_amt) {
        $this->_balancing_amt = $balancing_amt;
    }

    public function set_kdkegiatan($kdkegiatan) {
        $this->_kdkegiatan = $kdkegiatan;
    }

    public function set_nmkegiatan($nmkegiatan) {
        $this->_nmkegiatan = $nmkegiatan;
    }
	
	public function set_pengembalian($pengembalian) {
        $this->_pengembalian = $pengembalian;
    }
	public function set_tgl($tgl) {
        $this->_tgl = $tgl;
    }
	
	public function set_nodok($nodok) {
        $this->_nodok = $nodok;
    }
	public function set_mpn2_pajak($mpn2_pajak) {
        $this->_mpn2_pajak = $mpn2_pajak;
    }
	public function set_mpn1_pajak($mpn1_pajak) {
        $this->_mpn1_pajak = $mpn1_pajak;
    }
	public function set_mpn2_nonpajak($mpn2_nonpajak) {
        $this->_mpn2_nonpajak = $mpn2_nonpajak;
    }
	public function set_mpn1_nonpajak($mpn1_nonpajak) {
        $this->_mpn1_nonpajak = $mpn1_nonpajak;
    }
	public function set_bi_nonpajak($bi_nonpajak) {
        $this->_bi_nonpajak = $bi_nonpajak;
    }
	public function set_bi_pajak($bi_pajak) {
        $this->_bi_pajak = $bi_pajak;
    }
	public function set_dtc_nonpajak($dtc_nonpajak) {
        $this->_dtc_nonpajak = $dtc_nonpajak;
    }
	public function set_dtc_pajak($dtc_pajak) {
        $this->_dtc_pajak = $dtc_pajak;
    }

    /*
     * getter
     */
	public function get_jan() {
        return $this->_jan;
    }
	
	public function get_feb() {
        return $this->_feb;
    }
	
	public function get_mar() {
        return $this->_mar;
    }
	
	public function get_apr() {
        return $this->_apr;
    }
	
	public function get_mei() {
        return $this->_mei;
    }
	
	public function get_jun() {
        return $this->_jun;
    }
	
	public function get_jul() {
        return $this->_jul;
    }
	
	public function get_agt() {
        return $this->_agt;
    }
	
	public function get_sep() {
        return $this->_sep;
    }
	
	public function get_okt() {
        return $this->_okt;
    }
	
	public function get_nov() {
        return $this->_nov;
    }
	
	public function get_des() {
        return $this->_des;
    }
    public function get_obligation() {
        return $this->_obligation;
    }

    public function get_block_amount() {
        return $this->_block_amount;
    }

    public function get_temp_block() {
        return $this->_temp_block;
    }

    public function get_cash_limit() {
        return $this->_cash_limit;
    }

    public function get_invoice() {
        return $this->_invoice;
    }

    public function get_nm_satker() {
        return $this->_nm_satker;
    }

    public function get_satker() {
        return $this->_satker;
    }

    public function get_code_id() {
        return $this->_code_id;
    }

    public function get_kppn() {
        return $this->_kppn;
    }

    public function get_akun() {
        return $this->_akun;
    }

    public function get_program() {
        return $this->_program;
    }

    public function get_output() {
        return $this->_output;
    }

    public function get_dana() {
        return $this->_dana;
    }

    public function get_bank() {
        return $this->_bank;
    }

    public function get_kewenangan() {
        return $this->_kewenangan;
    }

    public function get_lokasi() {
        return $this->_lokasi;
    }

    public function get_budget_type() {
        return $this->_budget_type;
    }

    public function get_currency_code() {
        return $this->_currency_code;
    }

    public function get_budget_amt() {
        return $this->_budget_amt;
    }

    public function get_encumbrance_amt() {
        return $this->_encumbrance_amt;
    }

    public function get_actual_amt() {
        return $this->_actual_amt;
    }

    function get_block_revisi() {
        return $this->_block_revisi;
    }

    function set_block_revisi($_block_revisi) {
        $this->_block_revisi = $_block_revisi;
    }

    public function get_balancing_amt() {
        return $this->_balancing_amt;
    }

    public function get_kdkegiatan() {
        return $this->_kdkegiatan;
    }

    public function get_nmkegiatan() {
        return $this->_nmkegiatan;
    }
	public function get_pengembalian() {
        return $this->_pengembalian;
    }
	public function get_tgl() {
        return $this->_tgl;
    }
	public function get_nodok() {
        return $this->_nodok;
    }
	public function get_mpn1_pajak() {
        return $this->_mpn1_pajak;
    }
	public function get_mpn1_nonpajak() {
        return $this->_mpn1_nonpajak;
    }
	public function get_mpn2_nonpajak() {
        return $this->_mpn2_nonpajak;
    }
	public function get_mpn2_pajak() {
        return $this->_mpn2_pajak;
    }
	public function get_bi_nonpajak() {
        return $this->_bi_nonpajak;
    }
	public function get_bi_pajak() {
        return $this->_bi_pajak;
    }
	public function get_dtc_nonpajak() {
        return $this->_dtc_nonpajak;
    }
	public function get_dtc_pajak() {
        return $this->_dtc_pajak;
    }

    public function get_table1() {
        return $this->_table1;
    }

    /*
     * destruktor
     */

    public function __destruct() {
        
    }

}
